﻿using Finisar.SQLite;

namespace MyMovies
{
    internal class TableVideoFileInfo
    {
        private static string create = @"CREATE TABLE [VideoFileInfo] (
                                    [ID] INTEGER  NOT NULL PRIMARY KEY,
                                    [Format] VARCHAR(64)  NULL,
                                    [Duration] INTEGER  NULL,
                                    [Width] INTEGER  NULL,
                                    [Height] INTEGER  NULL,
                                    [AspectRatio] VARCHAR(32)  NULL
                                    )";

        public static bool Create()
        {
            return SqlLiteManagement.ExecuteNonQuery(create) >= 0;
        }

        public static bool FindByID(int id, ref MediaInfoVideo miv)
        {
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = id;

            string query = "SELECT [Format], [Duration], [Width], [Height], [AspectRatio] FROM [VideoFileInfo] WHERE [ID] = @p1";
            if (SqlLiteManagement.ExecuteReader(query, new SQLiteParameter[] { p1 }) != null)
            {
                if (SqlLiteManagement.Read())
                {
                    miv.Format = SqlLiteManagement.GetValue<string>("[Format]");
                    miv.Duration = SqlLiteManagement.GetValue<int>("[Duration]");
                    miv.Aspect = SqlLiteManagement.GetValue<string>("[AspectRatio]");
                    miv.Width = SqlLiteManagement.GetValue<int>("[Width]");
                    miv.Height = SqlLiteManagement.GetValue<int>("[Height]");
                }
                SqlLiteManagement.ReaderClose();
                return true;
            }
            return false;
        }

        public static bool Insert(int id, MediaInfoVideo miv)
        {
            SQLiteParameter pFormat = new SQLiteParameter("@pFormat", System.Data.DbType.String);
            SQLiteParameter pDuration = new SQLiteParameter("@pDuration", System.Data.DbType.String);
            SQLiteParameter pAspect = new SQLiteParameter("@pAspect", System.Data.DbType.String);
            SQLiteParameter pWidth = new SQLiteParameter("@pWidth", System.Data.DbType.Int16);
            SQLiteParameter pHeight = new SQLiteParameter("@pHeight", System.Data.DbType.Int16);
            SQLiteParameter pID = new SQLiteParameter("@pID", System.Data.DbType.Int16);

            pFormat.Value = miv.Format;
            pDuration.Value = miv.Duration;
            pAspect.Value = miv.Aspect;
            pWidth.Value = miv.Width;
            pHeight.Value = miv.Height;
            pID.Value = id;

            string query = "INSERT INTO [VideoFileInfo] ([ID], [Format], [Duration], [Width], [Height], [AspectRatio]) VALUES (@pID, @pFormat, @pDuration, @pWidth, @pHeight, @pAspect)";
            int result = SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] { pID, pFormat, pDuration, pWidth, pHeight, pAspect });

            return result != 0;
        }

        public static void RemoveALL()
        {
            string query = "DELETE FROM [VideoFileInfo]";
            SqlLiteManagement.ExecuteNonQuery(query);
        }

        public static void RemoveOld()
        {
            string query = "DELETE FROM [VideoFileInfo] WHERE [ID] NOT IN (SELECT [ID] FROM [Movies])";
            SqlLiteManagement.ExecuteNonQuery(query);
        }

        public static void RemoveByID(int id)
        {
            string query = "DELETE FROM [VideoFileInfo] WHERE [ID] = @p1";
            SQLiteParameter p1 = new SQLiteParameter("@p1", System.Data.DbType.Int16);
            p1.Value = id;
            SqlLiteManagement.ExecuteNonQuery(query, new SQLiteParameter[] {p1});
        }

        public static void Fill(System.Windows.Forms.ListView.ListViewItemCollection items, int ID)
        {
            MediaInfoVideo miv = new MediaInfoVideo();
            if (FindByID(ID, ref miv))
            {
                System.Windows.Forms.ListViewItem lvi = null;
                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.Format);
                lvi.SubItems.Add(miv.Format);
                items.Add(lvi);

                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.Duration);
                lvi.SubItems.Add(Decoder.Decode<int>(miv.Duration, Types.Duration));
                items.Add(lvi);

                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.Aspect);
                lvi.SubItems.Add(miv.Aspect);
                items.Add(lvi);

                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.Width);
                lvi.SubItems.Add(miv.Width.ToString());
                items.Add(lvi);

                lvi = new System.Windows.Forms.ListViewItem(Properties.Resources.Height);
                lvi.SubItems.Add(miv.Height.ToString());
                items.Add(lvi);
            }
        }
    }
}